package top.lbqaq.dao;

import com.mysql.cj.util.StringUtils;
import top.lbqaq.pojo.Supplier;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @author luoboQAQ
 * @Date 2021/11/30
 */
public class SupplierDao {

    public Supplier queryBySid(Connection connection, String sid) throws SQLException {
        Supplier supplier = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        if (connection != null) {
            String sql = "select * from supplier where sid = ?";
            Object[] params = {sid};

            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            if (rs.next()) {
                supplier = new Supplier();
                encapsulation(rs, supplier);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return supplier;
    }

    public List<Supplier> queryBySname(Connection connection, String sname) throws SQLException {
        List<Supplier> list = new LinkedList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;

        if (sname != null) {
            sname = "%" + sname + "%";
        }

        if (connection != null) {
            String sql = "select * from supplier where sname like ?";
            Object[] params = {sname};

            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                Supplier supplier = new Supplier();
                encapsulation(rs, supplier);
                list.add(supplier);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return list;
    }

    public List<Supplier> queryBySupply(Connection connection, String supply) throws SQLException {
        List<Supplier> list = new LinkedList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;

        if (supply != null) {
            supply = "%" + supply + "%";
        }

        if (connection != null) {
            String sql = "select * from supplier where supply like ?";
            Object[] params = {supply};

            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                Supplier supplier = new Supplier();
                encapsulation(rs, supplier);
                list.add(supplier);
            }
        }
        return list;
    }

    public List<Supplier> queryAll(Connection connection) throws SQLException {
        List<Supplier> list = new LinkedList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (connection != null) {
            String sql = "select * from supplier";
            rs = BaseDao.execute(connection, pstm, rs, sql, null);
            while (rs.next()) {
                Supplier supplier = new Supplier();
                encapsulation(rs, supplier);
                list.add(supplier);
            }
        }
        return list;
    }

    public int insert(Connection connection, Supplier supplier) throws SQLException {
        PreparedStatement pstm = null;
        int end = 0;

        if (connection != null) {
            String sql = "insert into supplier(sname,contact,supply,phone1,phone2,reputation,remark)" +
                    "values(?,?,?,?,?,?,?)";
            Object[] params = {supplier.getSname(), supplier.getContact(), supplier.getSupply(),
                    supplier.getPhone1(), supplier.getPhone2(), supplier.getReputation(), supplier.getRemark()};
            end = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return end;
    }

    public int updateBySid(Connection connection, Supplier supplier) throws SQLException {
        PreparedStatement pstm = null;
        int end = 0;

        if (connection != null) {
            String sql = "update supplier set sname=?,contact=?,supply=?," +
                    "phone1=?,phone2=?,reputation=?,remark=? " +
                    "where sid=?";
            Object[] params = {supplier.getSname(), supplier.getContact(), supplier.getSupply(),
                    supplier.getPhone1(), supplier.getPhone2(), supplier.getReputation(),
                    supplier.getRemark(), supplier.getSid()};
            end = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return end;
    }

    public int deleteBySid(Connection connection, String sid) throws SQLException {
        PreparedStatement pstm = null;
        int end = 0;

        if (connection != null) {
            String sql = "delete from supplier where sid=?";
            Object[] params = {sid};
            end = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return end;
    }

    public int getCount(Connection connection, String sname, String supply) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;

        if (connection != null) {
            StringBuilder sql = new StringBuilder();
            sql.append("select count(1) as count from supplier where 1=1 ");
            ArrayList<Object> list = new ArrayList<>();

            if (!StringUtils.isNullOrEmpty(sname)) {
                sql.append("and sname like ? ");
                list.add("%" + sname + "%");
            }

            if (!StringUtils.isNullOrEmpty(supply)) {
                sql.append("and supply like ? ");
                list.add("%" + supply + "%");
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            if (rs.next()) {
                count = rs.getInt("count");
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return count;
    }

    public List<Supplier> getList(Connection connection, String sname, String supply, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Supplier> list = new LinkedList<>();

        if (connection != null) {
            StringBuilder sql = new StringBuilder();
            sql.append("select * from supplier where 1=1 ");
            ArrayList<Object> paramList = new ArrayList<>();

            if (!StringUtils.isNullOrEmpty(sname)) {
                sql.append("and sname like ? ");
                paramList.add("%" + sname + "%");
            }

            if (!StringUtils.isNullOrEmpty(supply)) {
                sql.append("and supply like ? ");
                paramList.add("%" + supply + "%");
            }

            sql.append(" limit ?,?");
            currentPageNo = (currentPageNo - 1) * pageSize;
            paramList.add(currentPageNo);
            paramList.add(pageSize);

            Object[] params = paramList.toArray();
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            while (rs.next()) {
                Supplier supplier = new Supplier();
                encapsulation(rs, supplier);
                list.add(supplier);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return list;
    }

    private void encapsulation(ResultSet rs, Supplier supplier) throws SQLException {
        supplier.setSid(rs.getLong("sid"));
        supplier.setSname(rs.getString("sname"));
        supplier.setContact(rs.getString("contact"));
        supplier.setSupply(rs.getString("supply"));
        supplier.setPhone1(rs.getString("phone1"));
        supplier.setPhone2(rs.getString("phone2"));
        supplier.setReputation(rs.getString("reputation"));
        supplier.setRemark(rs.getString("remark"));
    }
}
